package com.mobile.framework.dao.hibernate;

import com.mobile.framework.dao.BaseDAO;
import com.mobile.framework.util.PageList;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 封装hibernate的DAO基类
 * <p/>
 * Revision History
 * <p/>
 * 2010-7-29,dean.xu,created it
 */
public abstract class BaseDAOHibernate extends HibernateDaoSupport implements
        BaseDAO {

    protected final Log log = LogFactory.getLog(getClass().getName());

    protected Class clazz;

    protected String entityName = null;

    public Class getClazz() {
        return clazz;
    }

    public void setClazz(Class clazz) {
        this.clazz = clazz;
    }

    public BaseDAOHibernate(Class clazz) {
        super();
        this.clazz = clazz;
    }

    public BaseDAOHibernate(String type) {
        super();
        this.entityName = type;
    }

    public BaseDAOHibernate() {
    }

    /**
     * 根据主键获得实体
     *
     * @param id
     * @return 实体对象
     */
    public Object getEntity(Long id) {
        Object o = null;
        if (entityName == null) {
            o = getHibernateTemplate().get(clazz, id);
        } else {
            o = getHibernateTemplate().get(entityName, id);
        }
        return o;
    }

    /**
     * 获得所有实体
     *
     * @return List
     */
    public List getAllEntity() {
        PageList result = new PageList();
        List l = null;
        if (entityName == null) {
            l = getHibernateTemplate().loadAll(clazz);
        } else {
            l = getHibernateTemplate().find(" FROM " + entityName);
        }
        result.addAll(l);
        result.setCurrentPage(1);
        result.setPageCount(1);
        result.setTotalRowCount(l.size());
        return result;
    }

    /**
     * 保存实体
     *
     * @param entity
     */
    public void saveEntity(Object entity) {
        if (entityName == null) {
            getHibernateTemplate().saveOrUpdate(entity);
        } else {
            getHibernateTemplate().saveOrUpdate(entityName, entity);
        }
    }

    /**
     * 根据主键删除实体
     *
     * @param id
     */
    public void removeEntity(Long id) {
        Object o = getEntity(id);
        if (null != o) {
            getHibernateTemplate().delete(o);
        }
    }

    /**
     * 执行批量更新和删除操作的HQL
     *
     * @param sql
     */
    public List executeHQL(String sql) {
        Session session = null;
        List ret = null;
        try {
            session = this.getSession();
            if (sql.toUpperCase().startsWith("DELETE")
                    || sql.toUpperCase().startsWith("UPDATE")) {
                session.createQuery(sql).executeUpdate();
            } else {
                ret = session.createQuery(sql).list();
            }
        } catch (HibernateException e) {
            log.error("executeHQL() error:" + sql, e);
            throw convertHibernateAccessException(e);
        } finally {
            this.releaseSession(session);
        }
        return ret;
    }

    public List executeHQLByParameters(String hql,Map parameters){
        Session session = null;
        List ret = null;
        try {
            session = this.getSession();
            Query q = session.createQuery(hql);
            if (hql.toUpperCase().startsWith("DELETE")
                    || hql.toUpperCase().startsWith("UPDATE")) {
                session.createQuery(hql).executeUpdate();
            } else {
                if (parameters != null){
                    for (Object o : parameters.keySet()) {
                        if (parameters.get(o) instanceof List) {
                            q.setParameterList(o.toString(), (List) parameters.get(o));
                        } else {
                            q.setParameter(o.toString(), parameters.get(o));
                        }
                    }
                }
                ret = q.list();
            }
        } catch (HibernateException e) {
            log.error("executeHQLByParameters() error:" + hql, e);
            throw convertHibernateAccessException(e);
        } finally {
            this.releaseSession(session);
        }
        return ret;
    }

    /**
     * 执行分页查询
     *
     * @param selectField HQL语句中，SELECT 的内容(如果设置了此参数值,则sql参数中不可带SELCT语句部分)
     * @param countField  HQL语句中，count 的内容
     * @param sql         HQL语句
     * @param page        第几页
     * @param rowsPerPage 每页记录数
     * @return PageList
     */
    public List pageListQuery(String selectField, String countField, String sql, int page, int rowsPerPage) {
        return this.pageListQuery(selectField, countField, sql, page, rowsPerPage, null);
    }

    /**
     * 执行分页查询
     *
     * @param selectField HQL语句中，SELECT 的内容(如果设置了此参数值,则sql参数中不可带SELCT语句部分)
     * @param countField  HQL语句中，count 的内容
     * @param sql         HQL语句
     * @param page        第几页
     * @param rowsPerPage 每页记录数
     * @param parameters  根据占位符查询的占位符集合
     * @return PageList
     */
    public List pageListQuery(String selectField, String countField, String sql, int page, int rowsPerPage, Map parameters) {
        PageList result = new PageList();
        Session session = null;
        try {
            session = this.getSession();
            String countSql = sql.substring(sql.toUpperCase().indexOf("FROM"));
            if (null != selectField) {
                sql = "SELECT " + selectField + sql;
            }
            if (page <= 0) {
                page = 1;
            }
            Query q = session.createQuery(sql);

            if (parameters != null){
                for (Object o : parameters.keySet()) {
                    if (parameters.get(o) instanceof List) {
                        q.setParameterList(o.toString(), (List) parameters.get(o));
                    } else {
                        q.setParameter(o.toString(), parameters.get(o));
                    }
                }
            }
            if (rowsPerPage > 0) {
                q.setFirstResult(rowsPerPage * (page - 1));
                q.setMaxResults(rowsPerPage);
            }
            result.addAll(q.list());

            if (null == countField) {
                countField = "*";
            }

            int rowsCount = result.size();
            if (rowsPerPage >= 1 && rowsCount > 0) {
                rowsCount = this.countHQL(countSql, countField, parameters, session);
            }

            if (0 == rowsCount) {
                page = 0;
            }
            if (rowsPerPage < 0) {
                page = 1;
                rowsPerPage = rowsCount;
            }

            result.setCurrentPage(page);
            result.setTotalRowCount(rowsCount);
            result.calcPageCount(rowsPerPage);

        } catch (Exception e) {
            e.printStackTrace();
            log.error("pageListQuery() error:" + sql, e);
            //throw convertHibernateAccessException(e);
        } finally {
            this.releaseSession(session);
        }
        return result;
    }


    /**
     * 执行分页查询
     *
     * @param sql         HQL语句
     * @param rowsPerPage 每页记录数
     * @param page        第几页
     * @return PageList
     */
    public List pageListQuery(String sql, int page, int rowsPerPage)
            throws HibernateException {
        return pageListQuery(null, null, sql, page, rowsPerPage);
    }

    /**
     * 执行分页查询
     *
     * @param countField  HQL语句中，count 的内容
     * @param sql         HQL语句
     * @param rowsPerPage 每页记录数
     * @param page        第几页
     * @return PageList
     */
    protected List pageListQuery(String countField, String sql, int page, int rowsPerPage) throws HibernateException {
        return pageListQuery(null, countField, sql, page, rowsPerPage);
    }

    /**
     * 计算HQL查询的返回记录数
     *
     * @param sql        查询语句
     * @param countField count语句操作的字段
     * @return 记录数
     */
    public int countHQL(String sql, String countField) {
        return this.countHQL(sql, countField, null);
    }

    public static void main(String[] arge) {

        String sql = "select count(idcard) FROM TkbOrder t1 WHERE 1=1 GROUP BY name,idcard,mphone";


        String regex_order_by = "\\s[G|g][R|r][O|o][U|u][P|p]\\s*[B|b][Y|y]";
        Pattern p = Pattern.compile(regex_order_by);
        Matcher m = p.matcher(sql);
        if (m.find()) {
            int oPos = sql.indexOf(m.group());
            if (oPos > 0) {
                sql = sql.substring(0, oPos);
            }
        }

        String regex_group_by = "\\s[G|g][R|r][O|o][U|u][P|p]\\s*[B|b][Y|y]\\s";
        p = Pattern.compile(regex_group_by);
        if (m.find()) {
            int oPos = sql.indexOf(m.group());
            if (oPos > 0) {
                sql = sql.substring(0, oPos);
            }
        }
        System.out.println(sql);
    }

    public int countHQL(String sql, String countField, Map parameters, Session session) {

        if (null == countField) {
            countField = "*";
        }

        sql = "select count(" + countField + ") " + sql;

        String regex_order_by = "\\s[O|o][R|r][D|d][E|e][R|r]\\s*[B|b][Y|y]\\s";
        Pattern p = Pattern.compile(regex_order_by);
        Matcher m = p.matcher(sql);
        if (m.find()) {
            int oPos = sql.indexOf(m.group());
            if (oPos > 0) {
                sql = sql.substring(0, oPos);
            }
        }

        String regex_group_by = "\\s[G|g][R|r][O|o][U|u][P|p]\\s*[B|b][Y|y]\\s";
        p = Pattern.compile(regex_group_by);
        m = p.matcher(sql);
        if (m.find()) {
            int oPos = sql.indexOf(m.group());
            if (oPos > 0) {
                sql = sql.substring(0, oPos);
            }
        }

        sql = Pattern.compile("\\s[F|f][E|e][T|t][C|c][H|h]\\s").matcher(sql).replaceAll(" ");
        //sql = Pattern.compile("\\s[G|g][R|r][O|o][U|u][P|p]\\s*[B|b][Y|y]").matcher(sql).replaceAll(" ");
        Query query = session.createQuery(sql);
        if (parameters != null)
            for (Object o : parameters.keySet()) {
                if (parameters.get(o) instanceof List) {
                    query.setParameterList(o.toString(), (List) parameters.get(o));
                } else {
                    query.setParameter(o.toString(), parameters.get(o));
                }
            }

        return ((Number) query.iterate().next()).intValue();

    }

    public int countHQL(String sql, String countField, Map parameters) {
        int rowsCount = 0;
        Session session = null;
        try {
            session = this.getSession();
            rowsCount = countHQL(sql, countField, parameters, session);
        } catch (HibernateException e) {
            log.error("countHQL() error:" + sql, e);
            throw convertHibernateAccessException(e);
        } finally {
            this.releaseSession(session);
        }
        return rowsCount;
    }

    /**
     * 计算HQL查询的返回记录数
     *
     * @param sql 查询语句
     * @return 记录数
     */
    public int countHQL(String sql) {
        return countHQL(sql, null);
    }

/**
     * 原生sql分页统计查询
     * @param sql
     * @param pageNum
     * @param pageRows
     * @param parameters
     * @return
     */
    public List pageListSQLQuery(String sql, int pageNum, int pageRows, Map parameters) {
        PageList result = new PageList();
        Session session = null;
        try {
            session = this.getSession();
            String countSql = "select count(0) from ( " + sql + " ) temp_t ";

            Query countQuery = session.createSQLQuery(countSql);
            if (parameters != null) {
                for (Object o : parameters.keySet()) {
                    if (parameters.get(o) instanceof List) {
                        countQuery.setParameterList(o.toString(), (List) parameters.get(o));
                    } else {
                        countQuery.setParameter(o.toString(), parameters.get(o));
                    }
                }
            }
            int rowsCount = Integer.valueOf(String.valueOf(countQuery.uniqueResult()));

            if (pageNum <= 0) {
                pageNum = 1;
            }

            if(rowsCount > 0){
                Query q = session.createSQLQuery(sql);

                if (parameters != null) {
                    for (Object o : parameters.keySet()) {
                        if (parameters.get(o) instanceof List) {
                            q.setParameterList(o.toString(), (List) parameters.get(o));
                        } else {
                            q.setParameter(o.toString(), parameters.get(o));
                        }
                    }
                }

                if (pageRows > 0) {
                    q.setFirstResult(pageRows * (pageNum - 1));
                    q.setMaxResults(pageRows);
                }
                result.addAll(q.list());
            }



            if (0 == rowsCount) {
                pageNum = 0;
            }
            if (pageRows < 0) {
                pageNum = 1;
                pageRows = rowsCount;
            }

            result.setCurrentPage(pageNum);
            result.setTotalRowCount(rowsCount);
            result.calcPageCount(pageRows);

        } catch (Exception e) {
            e.printStackTrace();
            log.error("pageListSQLQuery() error:" + sql, e);
        } finally {
            this.releaseSession(session);
        }
        return result;
    }
}